<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>找sql字段</title>
</head>
<style>
    .spanClass {
        /* 转化span模式，使span能设置宽高 */
        display: inline-block;
        width: 40px;
        height: 40px;
        background-color: #333;
        color: #fff;
        font-weight: 900;
        /* 使方块中文字居中 */
        text-align: center;
        line-height: 40px;
    }
</style>
<body>
<button onClick="testIndexOf(5)">点击joinPartitionBy()</button>
<div>
    <span class="spanClass day">1</span> <strong>天</strong>
    <span class="spanClass hour">1</span> <strong>小时</strong>
    <span class="spanClass minute">2</span> <strong>分钟</strong>
    <span class="spanClass second">3</span> <strong>秒</strong>
</div>
<!--<script src="../js/moment.min.js"/>-->
<script>
    // 获取分区字段
    function getSqlFieldArr(){
        let incrementalScript = '--=================================================--\n' +
            '-- Table: DDWUSER.T_MDW_T01_TABLE_798\n' +
            '-- Definition: \n' +
            '--=================================================--\n' +
            'CREATE TABLE IF NOT EXISTS DDWUSER.T_MDW_T01_TABLE_798 (\n' +
            '\tCOLUMN_1\tSTRING\tCOMMENT \'员工编码\',\n' +
            '\tCOLUMN_2\tSTRING\tCOMMENT \'员工名称\',\n' +
            '\tCOLUMN_3\tSTRING\tCOMMENT \'员工工号\',\n' +
            ' PRIMARY KEY (COLUMN_1) DISABLE NOVALIDATE\n' +
            ')\n' +
            'COMMENT \'员工维表\';'

        const CONSTRAINT = 'CONSTRAINT'
        console.log(incrementalScript)

        let protoSql = incrementalScript
        console.log('protoSql', protoSql)
        // 去掉注释 找建表语句
        // 建表语句开始下标 createBeginIndex + 3
        let createTableBeginIndex = protoSql.lastIndexOf('*/') + 3
        console.log('建表语句开始下标', createTableBeginIndex)
        // 建表语句
        let createTableSql = protoSql.slice(createTableBeginIndex)
        console.log('建表语句\n', createTableSql)
        // 找到建表语句的第一个小括号左 (
        let fieldBeginIndex =  createTableSql.indexOf('(') + 2
        // 找到建表语句的第二个小括号右 )
        let fieldEndIndex =  createTableSql.lastIndexOf(')')
        console.log('字段sql的开始和结束下标', fieldBeginIndex,fieldEndIndex)
        // 字段sql
        let fieldSql = createTableSql.slice(fieldBeginIndex,fieldEndIndex)
        console.log('字段sql\n', fieldSql)
        // console.log('EQTY_IMT_BALT_ID\tINTEGER\tNOT NULL\tCOMMENT \'Equity Instrument Ballot Id\','.match(/^\w+/gms))
        // 根据逗号分割字符串位数组,且循环找字段。
        let fieldLineArr = fieldSql.split(',\n')
        // 存放字段
        let fieldArr = []
        let fieldDetailArr = []
        for(let fieldLine of fieldLineArr) {
            //console.log(fieldLine)
            let fieldName = fieldLine.replace('\n','').substr(1).match(/^\w+/g)
            if(fieldName[0] !== CONSTRAINT){
                fieldArr.push(fieldName[0])
                // 分割字段与类型
                let fieldDetail =  fieldLine.replace('\n','').substr(1).split('\t')
                console.log('fieldDetail:', fieldDetail)
                let detail = {
                    [fieldDetail[0]]: fieldDetail[1]
                }
                fieldDetailArr.push(detail)
            }
        }
        console.log('字段数组', fieldArr)
        console.log('字段数组详细对象', fieldDetailArr)
    }



    // 范围分区 拼接 分区
    function joinPartitionBy() {
        getPartitionedBy(2023,2025)
        //calculateBucketNum(5000000)
        //console.log(calculateBucketNum(100000000))
        //console.log(getDateArrByRangeYear('2023','2025'))
    }
    function getPartitionedBy(beginYear,endYear){
        let dateArr = getDateArrByRangeYear(beginYear,endYear)
        let dateNumArr = getDateNumArrByRangeYear(beginYear,endYear)
        // PARTITION partition_before2025 VALUES LESS THAN ('2025-01-01')
        let partitionArr = []
        for (let i = 0; i < dateArr.length; i++) {
            let dataStr = dateArr[i]
            partitionArr.push('PARTITION partition_' + dateNumArr[i] + ' VALUES LESS THAN (' + dataStr + '\') \n' )

        }
        // 计算桶
        let BucketNum = calculateBucketNum(100000000)
        let result = 'PARTITIONED BY RANGE (FDATE) ( \n' + partitionArr.toString() + ') \n' + 'CLUSTERED BY (RISK_FTOR_CODE)\n' +
            'INTO '+ BucketNum +' BUCKETS\n' +
            'STORED AS ORC\n' +
            'TBLPROPERTIES (\'transactional\'=\'true\');'
        console.log(result)
    }
    //返回  2023-01-01 数组字符
    function getDateArrByRangeYear(startDate,endDate) {
        startDate = parseInt(startDate)
        endDate = parseInt(endDate)
        var dateArry = new Array()
        let mCount = (endDate - startDate) * 12 + 1 * 12 + 1
        if (mCount > 0) {
            var startM = 1
            var startY = startDate
            for (var i = 0; i < mCount; i++) {
                if (startM < 12) {
                    dateArry[i] = startY + "-" + (startM>9 ? startM : "0" + startM) + "-" + ("01")
                    startM += 1
                } else {
                    dateArry[i] = startY + "-" + (startM > 9 ? startM : "0" + startM) + "-" + ("01")
                    startM = 1
                    startY += 1
                }
            }
        }
        return dateArry
    }
    // 返回  202301 数组字符
    function getDateNumArrByRangeYear(startDate,endDate) {
        startDate = parseInt(startDate)
        endDate = parseInt(endDate)
        var dateArry = new Array()
        dateArry[0] = 'before'+startDate
        let mCount = (endDate - startDate) * 12 + 1 * 12 + 1
        if (mCount > 0) {
            var startM = 1
            var startY = startDate
            for (var i = 1; i < mCount; i++) {
                if (startM < 12) {
                    dateArry[i] = startY + (startM > 9 ? '' + startM : "0" + startM)
                    startM += 1
                } else {
                    dateArry[i] = startY + (startM > 9 ? '' + startM : "0" + startM)
                    startM = 1
                    startY += 1
                }
            }
        }
        return dateArry
    }

    //计算桶
    function isPrime(src){
        if (src < 2) {
            return false;
        }
        if (src == 2 || src == 3) {
            return true;
        }
        if (src % 2 == 0) {// 先判断是否为偶数，若偶数就直接结束程序
            return false;
        }
        let sqrt = Math.sqrt(src);
        for (let i = 3; i <= sqrt; i += 2) {
            if (src % i == 0) {
                return false;
            }
        }
        return true;
    }
    function calculateBucketNum(dataVolPerPart){
        let DATA_VOL_PER_BUCKET = 350 * 10000;
        let bucketNum =  parseInt((dataVolPerPart / DATA_VOL_PER_BUCKET + 1));
        if (bucketNum < 2) return 1;
        if (bucketNum == 2) return 3;
        while (!isPrime(bucketNum) || bucketNum % 31 == 0) {
            bucketNum += 1;
        }
        return bucketNum;
    }


    //删除表语句里的 字段
    function deleteFieldLine(field) {
        let incrementalScript = '/* ================================================ */\n' +
            '/* Table: DRVTIMTAR_ROLLDOWN */\n' +
            '/* Definition: Rollup Table to migrate attributes of FNC_IMT_AR to any suitable subtypes. */\n' +
            '/* */\n' +
            '/*To migrate the atributes, create a physical-only relationship to the required subtypes. */\n' +
            '/* */\n' +
            '/*This is a physical-only table. */\n' +
            '/* ================================================= */\n' +
            'CREATE TABLE IF NOT EXISTS BDW32.DRVTIMTAR_ROLLDOWN (\n' +
            '\tLEG_IDENT\tSMALLINT\tNOT NULL\tCOMMENT \'Leg Identifier\',\n' +
            '\tFIX_DT\tDATE\tNOT NULL\tCOMMENT \'Fixing Date\',\n' +
            '\tCNTPR_ID\tINTEGER\tNOT NULL\tCOMMENT \'Counterparty Id\',\n' +
            '\tORIG_TDG_TXN_ID\tINTEGER\tNOT NULL\tCOMMENT \'Originating Trading Transaction Id\',\n' +
            '\tPRTFL_ID\tSMALLINT\tNOT NULL\tCOMMENT \'Portfolio Id\',\n' +
            '\tTRD_IDENT\tINTEGER\tNOT NULL\tCOMMENT \'Trade Identifier\',\n' +
            '\tINR_DEAL_F\tSMALLINT\tNOT NULL\tCOMMENT \'Internal Deal Flag\',\n' +
            '\tSETL_DT\tDATE\tNOT NULL\tCOMMENT \'Settlement Date\',\n' +
            '\tCLRG_HS_F\tSMALLINT\tNOT NULL\tCOMMENT \'Clearing House Flag\',\n' +
            '\tTRD_DT\tDATE\tNOT NULL\tCOMMENT \'Trade Date\',\n' +
            '\tSTOP_LMT\tNUMERIC(15,2)\tNOT NULL\tCOMMENT \'Stop Limit\',\n' +
            '\tFND_AR_ID\tINTEGER\tNOT NULL\tCOMMENT \'XXX Funding Arrangement Id XXX\',\n' +
            '\tSTOP_LOSS_LMT\tNUMERIC(15,2)\tNOT NULL\tCOMMENT \'Stop Loss Limit\',\n' +
            '\tNXT_CALL_DT\tDATE\tNOT NULL\tCOMMENT \'Next Call Date\',\n' +
            '\tOwnedCorrected1\tINTEGER\tNOT NULL\tCOMMENT \'XXX Funding Arrangement Id XXX2\',\n' +
            '\tHLDG_PRD\tSMALLINT\tNOT NULL\tCOMMENT \'Holding Period\',\n' +
            '\tCONSTRAINT XPKE_10 PRIMARY KEY (LEG_IDENT,FIX_DT,CNTPR_ID,ORIG_TDG_TXN_ID,PRTFL_ID,TRD_IDENT,INR_DEAL_F,SETL_DT,CLRG_HS_F,TRD_DT,STOP_LMT,FND_AR_ID,STOP_LOSS_LMT,NXT_CALL_DT,OwnedCorrected1,HLDG_PRD),\n' +
            '\tCONSTRAINT XIF1E_10 FOREIGN KEY (CNTPR_ID,PRTFL_ID,ORIG_TDG_TXN_ID,TRD_IDENT,INR_DEAL_F,CLRG_HS_F,SETL_DT,TRD_DT,STOP_LMT,STOP_LOSS_LMT,OwnedCorrected1,NXT_CALL_DT,HLDG_PRD,FND_AR_ID) REFERENCES FNCIMT_AR_ROLLDOWN(CNTPR_ID,PRTFL_ID,ORIG_TDG_TXN_ID,TRD_IDENT,INR_DEAL_F,CLRG_HS_F,SETL_DT,TRD_DT,STOP_LMT,STOP_LOSS_LMT,OwnedCorrected1,FND_AR_ID,NXT_CALL_DT,HLDG_PRD)\n' +
            ')\n' +
            'ENGINE=InnoDB\n' +
            'DEFAULT CHARACTER SET = utf8\n' +
            'COLLATE=utf8_bin\n' +
            'COMMENT=\'E/10\'\n'

        let protoSql = incrementalScript
        console.log('protoSql', protoSql)
        // 去掉注释 找建表语句
        // 建表语句开始下标 createBeginIndex + 3
        let createTableBeginIndex = protoSql.lastIndexOf('*/') + 3
        console.log('建表语句开始下标', createTableBeginIndex)
        // 注释
        let comment = protoSql.slice(0,createTableBeginIndex)
        console.log('注释：\n', comment)
        // 建表语句
        let createTableSql = protoSql.slice(createTableBeginIndex)
        console.log('建表语句\n', createTableSql)
        // 找到建表语句的第一个小括号左 (
        let fieldBeginIndex =  createTableSql.indexOf('(') + 2
        // 找到建表语句的第二个小括号右 )
        let fieldEndIndex =  createTableSql.lastIndexOf(')')
        console.log('字段sql的开始和结束下标', fieldBeginIndex,fieldEndIndex)
        // 建表头语句
        let createHeadSql =  createTableSql.slice(0, fieldBeginIndex - 1)
        console.log('create表头：', createHeadSql)
        // 末尾配置项
        let lastComment = createTableSql.slice(fieldEndIndex + 2)
        console.log('末尾配置项:', lastComment)
        // 字段sql
        let fieldSql = createTableSql.slice(fieldBeginIndex, fieldEndIndex)
        console.log('字段sql\n', fieldSql)
        // 根据逗号分割字符串位数组,且循环找字段。
        let fieldLineArr = fieldSql.split('\n\t')
        // 存放字段
        let fieldArr = []
        for(let fieldLine of fieldLineArr) {
            //console.log(fieldLine)
            let fieldName = fieldLine.replace('\n', '').substr(0).match(/^\w+/g)
            if(fieldName[0] !== field){
                fieldArr.push('\n\t' + fieldLine)
            }
        }
        // 拼接
        let deletedFieldSql = comment + createHeadSql + fieldArr.toString() + ')\n' + lastComment
        console.log(deletedFieldSql)
        return deletedFieldSql
    }

    //31或者其倍数 + 1  ；例如： 31 得 32   62 得63 
    function f31MultiplePlus1(num) {
        if (num % 31 == 0) {
            num += 1
        }
        console.log(num)
        return num
    }

    function f(a) {
        if(a >= 1){
            console.log(a)
            f(--a)
        }
        console.log(a)
    }


    //1、获取元素
  /*  var hour = document.querySelector('.hour');//小时的黑色盒子
    var minute = document.querySelector('.minute');//分钟的黑色盒子
    var second = document.querySelector('.second');//秒数的黑色盒子*/
    endDateTime = '2023-03-24 13:00:00'
    // '2023-03-24 16:15:36'

    //countDown();                         //先调用一次防止第一次刷新页面有空白
    var i = 0;
    //2、开启定时器
    setInterval(countDown, 1000);         //每隔一秒调用一次
    function countDown() {
        debugger
        //获取元素
        var day = document.querySelector(".day");
        var hour = document.querySelector(".hour");
        var minute = document.querySelector(".minute");
        var second = document.querySelector(".second");
        var inputTime = + new Date(endDateTime);
        let now =+ new Date();
        let endDate = inputTime;
        let leftTime = endDate - now;//计算剩余的毫秒数
        if (leftTime <= 0) {
            leftTime = 0;
        }
        let leftsecond = parseInt(leftTime / 1000);//计算剩余的秒数
        var d = Math.floor(leftsecond / (60 * 60 * 24));
        let h = Math.floor((leftsecond - d * 24 * 60 * 60) / 3600);
        let m = Math.floor((leftsecond - d * 24 * 60 * 60 - h * 3600) / 60);
        let s = Math.floor(leftTime / 1000 % 60, 10);

        day.innerHTML = d < 10 ? "0" + d : d;
        hour.innerHTML = h < 10 ? "0" + h : h;
        minute.innerHTML = m < 10 ? "0" + m : m;
        second.innerHTML = s < 10 ? "0" + s : s;
        console.log(`${day}天${hour}小时${minute}分钟${second}秒`)
        /*var h = 0
        if(times / 60 / 60 > 23){
            h = parseInt(times / 60 / 60)
        }else {
            h = parseInt(times / 60 / 60 % 24);
        }
        //如果小时数小于 10，要变成 0 + 数字的形式 赋值给盒子
        hour.innerHTML = h < 10 ? "0" + h : h;
        //计算分钟数 转化为整数
        var m = parseInt(times / 60 % 60);
        //如果分钟数小于 10，要变成 0 + 数字的形式 赋值给盒子
        minute.innerHTML = m < 10 ? "0" + m : m;
        //计算描述 转化为整数
        var s = parseInt(times % 60);
        //如果秒钟数小于 10，要变成 0 + 数字的形式 赋值给盒子
        second.innerHTML = s < 10 ? "0" + s : s;*/
    }




</script>
</body>
</html>